/*
	exec sp_vendor_shipment_report_proc '380','0','sricustomer,sriendcust','0','',1,'0','0','crystal','0'
	exec sp_vendor_shipment_report_proc '0','0','Foxconn','0','',1,'0','04/01/2007','XLsREPORT','0'

	here if we add product search also it would be better for comparing with other shipment reports.
*/
alter PROC sp_vendor_shipment_report_proc

@customerIDs	varchar(200),
@vendorIDS		varchar(200),
@endcustomerids	varchar(500),/*here we are getting end customer name as param*/
@sonumber		varchar(50),
@productIDS		varchar(50),
@orderType		int,
@fromdate		varchar(50),
@todate			varchar(50),
@REPORT_TYPE	VARCHAR(20),
@defaultPrompt	varchar(50)

as
BEGIN
	create table  #vendor_ship_table
			([SALEORDER NO] VARCHAR(50), [CUSTOMER PO NO] VARCHAR(50),[BOOKING DATE] DATETIME,
			 [END CUSTOMER] VARCHAR(50),CUSTOMER VARCHAR(50),[END USER] VARCHAR(50),
			 [BOOKING DATE WORK WEEK NO] INT,[BOOKING DATE QUARTER] INT,  
			 [LINE ITEM NUMBER] INT,[COOL QTY] INT,[UNIT COST] FLOAT, [QTY SHIPPED] INT, 
			 [PART NUMBER] VARCHAR(50),[LOT NUMBER] VARCHAR(50),[LOT QTY PULLED] INT,
			 [SHIP DATE] DATETIME, [VEN LOC ADDRESS] INT,[INVOICE NUMBER] VARCHAR(50), 
			 [SHIP DATE WORK WEEK NO] INT,[SHIP DATE QUARTER] INT,[VENDOR NAME] VARCHAR(50))

	declare @sqlstring nvarchar(4000)
	CREATE TABLE #ADDRESS_TABLE ( ADDRESS_ID INT,VENDOR_ID INT,VENDOR_NAME VARCHAR(50))

	INSERT INTO #ADDRESS_TABLE
	SELECT L.ADDRESS_ID,V.VENDOR_ID,V_NAME FROM LOCATION L, VENDOR V WHERE L.VENDOR_ID = V.VENDOR_ID AND L_TYPE = 'FINISHED GOODS'
	--SELECT * FROM #ADDRESS_TABLE
	set @sqlstring = N'insert into #vendor_ship_table
	SELECT
   		salesOrder_no,so_customer_po_number,so_booking_date,
		(CASE 	
			WHEN ( SO.SO_ODM_ID IS NULL OR SO.SO_ODM_ID <= 0  ) THEN  C_NAME 
			ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ODM_ID )
 		END),customer.c_name,
		(CASE 
			WHEN ( SO.SO_ENDCUSTOMER_ID IS NULL OR SO.SO_ENDCUSTOMER_ID <= 0  ) THEN 
				(CASE WHEN ( SO.SO_ODM_ID IS NULL OR SO.SO_ODM_ID <= 0 ) THEN C_NAME 
				 	ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID =  SO.SO_ODM_ID )
			END)
		ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ENDCUSTOMER_ID)
		END),SO_BOOKING_DATE_WW_NO,SO_BOOKING_DATE_QTR_NO,cool_line_item_no,
		cool_qty,cool_unit_cost,cslit_qty_shipped,part_number,
		(select lot_number from lot_table where lot_id = Customer_Shipping_Lot_Number_Table.lot_id) as lot_number,
		cslnt_qty_pull,CST_SHIP_DATE,CST_LOCATION_ADDRESS_ID,inv_no,
		CST_SHIP_DATE_WW_NO,CST_SHIP_DATE_QTR_NO,VENDOR_NAME
	FROM
		  Sales_Order so
		INNER JOIN Customer ON so.Customer_ID = Customer.Customer_ID 
	     INNER JOIN  Customer_Open_Order_LineItem ON
	        so.SalesOrder_ID = Customer_Open_Order_LineItem.SalesOrder_ID
	     INNER JOIN Customer_Shipping_Line_Item_Table ON
	        Customer_Open_Order_LineItem.LineItem_ID = Customer_Shipping_Line_Item_Table.LineItem_ID
	     INNER JOIN  Product ON
	        Customer_Open_Order_LineItem.Product_ID = Product.Product_ID
	     INNER JOIN  Customer_Shipping_Lot_Number_Table ON
	        Customer_Shipping_Line_Item_Table.CSLIT_ID = Customer_Shipping_Lot_Number_Table.CSLIT_ID
	     INNER JOIN Customer_Shipping_Table ON
	        Customer_Shipping_Line_Item_Table.CustomerShipping_ID = Customer_Shipping_Table.CustomerShipping_ID
		 INNER JOIN #ADDRESS_TABLE AT ON
			Customer_Shipping_Table.CST_LOCATION_ADDRESS_ID = AT.ADDRESS_ID 
	
	where --Customer_Shipping_Lot_Number_Table.cslit_id = 24 and
		 Customer_Shipping_Table.cst_status= ''SHIPPED'' and Customer_shipping_line_item_table.CSLIT_Status = ''SHIPPED'' 
		 and  cslnt_qty_pull > 0 '

	if @customerIDs is not null and @customerIDs <> '0'
	begin
		set @sqlstring = @sqlstring + ' and customer.customer_id in ( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING ( '''+ @customerIDs+''') )'
	end

	if @vendorIDS is not null and @vendorIDS <> '0'
	begin
		set @sqlstring = @sqlstring + ' and VENDOR_ID in ( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING ( '''+ @vendorIDS+''') )'
	end

	if @endcustomerids is not null and @endcustomerids <> '0'
	begin
		set @sqlstring = @sqlstring + ' AND (CASE 	
							WHEN ( SO.SO_ODM_ID IS NULL OR SO.SO_ODM_ID <= 0  ) THEN  
							( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.CUSTOMER_ID )
							ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ODM_ID )
				 		END) IN ( SELECT VENDOR FROM FN_GET_STRINGS_IN_STRING ('''+ @endcustomerids +'''))'
	end

	if @sonumber is not null and @sonumber <> '0'
	begin
		set @sqlstring = @sqlstring + ' and salesOrder_no = '''+ @sonumber+''''
	end

	if @orderType is not null and @orderType <> -1 and len(ltrim(rtrim(@orderType))) > 0
	begin
		set @sqlstring = @sqlstring + ' and so.SO_Is_Sample = '''+ convert(varchar,@orderType)+''''
	end

	IF ( @fromdate IS NOT NULL AND @fromdate <> '0' )
	BEGIN
		SET @sqlstring = @sqlstring +' AND CST_SHIP_DATE >= '''+ CONVERT(VARCHAR,@fromdate, 101)+ ' 12:00AM ' +''''
	END

	IF ( @todate IS NOT NULL AND @todate <> '0' )
	BEGIN
		SET @sqlstring = @sqlstring +' AND CST_SHIP_DATE <= '''+ CONVERT(VARCHAR,@todate, 101)+ ' 11:59PM ' +''''
	END
	
	--print @sqlstring
	exec (@sqlstring)

	IF @REPORT_TYPE = 'XLREPORT'
	BEGIN
		SELECT 	
			[VENDOR NAME],CUSTOMER,[END CUSTOMER] AS [CM/ODM], [END USER] AS [End Customer/OEM],[SALEORDER NO],[LINE ITEM NUMBER], 
			[CUSTOMER PO NO], [BOOKING DATE],[BOOKING DATE WORK WEEK NO],[BOOKING DATE QUARTER], 
			[SHIP DATE],[SHIP DATE WORK WEEK NO],[SHIP DATE QUARTER],[PART NUMBER],[COOL QTY], 
			/*[UNIT COST],*/ [QTY SHIPPED], [LOT NUMBER], [LOT QTY PULLED],[INVOICE NUMBER] 
		FROM 
			#vendor_ship_table
		ORDER BY [SALEORDER NO],[LINE ITEM NUMBER],[LOT NUMBER]
	END
	ELSE
	BEGIN
		SELECT 	
			[SALEORDER NO], [CUSTOMER PO NO], [BOOKING DATE], [END CUSTOMER], [END USER],CUSTOMER, 
			[LINE ITEM NUMBER], [COOL QTY],	[UNIT COST], [QTY SHIPPED], [PART NUMBER], 
			[LOT NUMBER], [LOT QTY PULLED], [SHIP DATE], [INVOICE NUMBER], [VENDOR NAME]
		FROM 
			#vendor_ship_table
	END

END